package com.example.javaairportflightanalysis.dao;

import com.example.javaairportflightanalysis.domin.dto.DfdlDto;
import com.example.javaairportflightanalysis.domin.dto.DfdlDto2;
import com.example.javaairportflightanalysis.domin.merge.DfdlStatusDto;
import com.example.javaairportflightanalysis.domin.merge.TransferStatusDto;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;

import java.util.ArrayList;


public interface DfdlMapper {

    @Select("SELECT DISTINCT DAA.APNO, MDDD.DFLT_FLNO, MDDD.DFLT_AWCD, DAA.APCD, MDDD.DTIME, DAA.FPTT, DAA.FRTT,\n" +
            "         MDDD.AIRL_ARPT_ID, MDDD.DFLT_FLID\n" +
            "FROM zbtj.vw_MSG_DFOE_DFDL_DFLT MDDD\n" +
            "JOIN zbtj.\"DFOE_AIRL_ARPT\" DAA ON MDDD.AIRL_ARPT_ID = DAA.AIRL_ARPT_ID \n" +
            "WHERE MDDD.DFLT_FLIO = 'A' \n" +
            "AND EXISTS (\n" +
            "    SELECT 1 \n" +
            "    FROM zbtj.\"DFOE_AIRL_ARPT\" DAA2 \n" +
            "    WHERE DAA2.AIRL_ARPT_ID = MDDD.AIRL_ARPT_ID AND DAA2.FRTT <> ''\n" +
            ");")
    ArrayList<DfdlDto2> getA();
    @Select("SELECT DISTINCT DAA.APNO, \n" +
            "MDDD.DFLT_FLNO, \n" +
            "MDDD.DFLT_AWCD, \n" +
            "DAA.APCD, \n" +
            "MDDD.DTIME, \n" +
            "DAA.FPTT, \n" +
            "d.FRTT,\n" +
            "MDDD.AIRL_ARPT_ID, \n" +
            "MDDD.DFLT_FLID\n" +
            "FROM zbtj.vw_MSG_DFOE_DFDL_DFLT MDDD\n" +
            "JOIN zbtj.\"DFOE_AIRL_ARPT\" DAA ON MDDD.AIRL_ARPT_ID = DAA.AIRL_ARPT_ID\n" +
            "JOIN zbtj.VW_META_DFME_DEPE d ON MDDD.DFLT_FLID = d.FLID;")
    ArrayList<DfdlDto2> getD();

    /**
     * 第一种 为A的2403 国内航班
     *  AND DAA.APAT=2403
     */
    @Select("SELECT DISTINCT DAA.APNO,MDDD.DFLT_CFNO,MDDD.DFLT_FLNO, MDDD.DFLT_AWCD,DAA.APCD,MDDD.DTIME,DAA.FPTT,DAA.FPLT\n" +
            ",MDDD.AIRL_ARPT_ID\n" +
            ",DAA.APAT\n" +
            ",MDDD.DFLT_FLIO\n" +
            ",MDDD.DFLT_FLID\n" +
            "FROM zbtj.vw_MSG_DFOE_DFDL_DFLT MDDD\n" +
            "JOIN zbtj.\"DFOE_AIRL_ARPT\" DAA ON MDDD.AIRL_ARPT_ID= DAA.AIRL_ARPT_ID \n" +
            "WHERE MDDD.DFLT_FLIO = 'A';")
    ArrayList<DfdlDto> getA2401();
    /**
     * 第三种 为D的2401 国际航班
     */
    @Select("SELECT DISTINCT DAA.APNO,MDDD.DFLT_CFNO,MDDD.DFLT_FLNO, MDDD.DFLT_AWCD,DAA.APCD,MDDD.DTIME,DAA.FPTT,DAA.FPLT\n" +
            ",MDDD.AIRL_ARPT_ID\n" +
            ",DAA.APAT\n" +
            ",MDDD.DFLT_FLIO\n" +
            ",MDDD.DFLT_FLID\n" +
            "FROM zbtj.vw_MSG_DFOE_DFDL_DFLT MDDD\n" +
            "JOIN zbtj.\"DFOE_AIRL_ARPT\" DAA ON MDDD.AIRL_ARPT_ID= DAA.AIRL_ARPT_ID \n" +
            "WHERE MDDD.DFLT_FLIO = 'D';")
    ArrayList<DfdlDto> getD2401();

    @Select("select * from zbtj.Dfdl_A")
    ArrayList<DfdlStatusDto> getAapno2();

    @Select("select * from zbtj.Dfdl_D")
    ArrayList<DfdlStatusDto> getDapno2();

    @Select("select * from zbtj.Transfer_A")
    ArrayList<TransferStatusDto> getAapno3();

    @Select("select * from zbtj.Transfer_D")
    ArrayList<TransferStatusDto> getDapno3();

    @Select("select * from zbtj.Dfdl_A where id = #{id}")
    DfdlStatusDto selectAById(Integer id);

    /**
     * 审核中，根据id查询A中转需要更改的原始数据
     */
    @Select("select * from zbtj.Transfer_A where id = #{id}")
    TransferStatusDto selectTransferAById(Integer id);

    @Select("select * from zbtj.Dfdl_D where id = #{id}")
    DfdlStatusDto selectDById(Integer id);

    @Select("select * from zbtj.Transfer_D where id = #{id}")
    TransferStatusDto selectTransferDById(Integer id);



    @Insert("INSERT INTO zbtj.Dfdl_A (DFLT_FLNO,DFLT_FLID, DFLT_AWCD, APCD, DTIME, FPTT, FPLT, STATUS) " +
            "VALUES (#{dfltFlno}, #{dfltFlid},#{dfltAwcd}, #{apcd}, #{dtime}, #{fptt}, #{fplt}, #{status})")
    Integer insertA2Dfdl(DfdlStatusDto dfdlStatusDto);

    @Insert("INSERT INTO zbtj.Dfdl_D (DFLT_FLNO,DFLT_FLID,  DFLT_AWCD, APCD, DTIME, FPTT, FPLT, STATUS) " +
            "VALUES (#{dfltFlno},  #{dfltFlid},#{dfltAwcd}, #{apcd}, #{dtime}, #{fptt}, #{fplt}, #{status})")
    Integer insertD2Dfdl(DfdlStatusDto dfdlStatusDto);

    @Insert("INSERT INTO zbtj.Transfer_A (dfltFlno, DFLT_FLID, dfltAwcd, transitApcd, apcd, dtime, fptt, fplt, STATUS) " +
            "VALUES (#{dfltFlno}, #{dfltFlid},#{dfltAwcd}, #{transitApcd}, #{apcd}, #{dtime}, #{fptt}, #{fplt}, #{status})")
    Integer insertA3Dfdl(TransferStatusDto transferStatusDto);

    @Insert("INSERT INTO zbtj.Transfer_D (dfltFlno, DFLT_FLID, dfltAwcd, transitApcd, apcd, dtime, fptt, fplt, STATUS) " +
            "VALUES (#{dfltFlno}, #{dfltFlid},#{dfltAwcd}, #{transitApcd}, #{apcd}, #{dtime}, #{fptt}, #{fplt}, #{status})")
    Integer insertD3Dfdl(TransferStatusDto transferStatusDto);



}
